/*==============================================================*/
/* DBMS name:      Microsoft SQL Server 2012                    */
/* Created on:     2020/10/7 9:00:55                            */
/*==============================================================*/


if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('BackSale') and o.name = 'FK_BACKSALE_REFERENCE_DEPT')
alter table BackSale
   drop constraint FK_BACKSALE_REFERENCE_DEPT
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('BackSale') and o.name = 'FK_BACKSALE_REFERENCE_STOREHOU')
alter table BackSale
   drop constraint FK_BACKSALE_REFERENCE_STOREHOU
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('BackSale') and o.name = 'FK_BACKSALE_REFERENCE_EMPLOYEE')
alter table BackSale
   drop constraint FK_BACKSALE_REFERENCE_EMPLOYEE
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('BackSale_Detail') and o.name = 'FK_BACKSALE_REFERENCE_BACKSTOC')
alter table BackSale_Detail
   drop constraint FK_BACKSALE_REFERENCE_BACKSTOC
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('BackSale_Detail') and o.name = 'FK_BACKSALE_REFERENCE_PRODUCT')
alter table BackSale_Detail
   drop constraint FK_BACKSALE_REFERENCE_PRODUCT
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('BackStock') and o.name = 'FK_BACKSTOC_REFERENCE_DEPT')
alter table BackStock
   drop constraint FK_BACKSTOC_REFERENCE_DEPT
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('BackStock') and o.name = 'FK_BACKSTOC_REFERENCE_STOREHOU')
alter table BackStock
   drop constraint FK_BACKSTOC_REFERENCE_STOREHOU
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('BackStock') and o.name = 'FK_BACKSTOC_REFERENCE_EMPLOYEE')
alter table BackStock
   drop constraint FK_BACKSTOC_REFERENCE_EMPLOYEE
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('BackStock_Detail') and o.name = 'FK_BACKSTOC_REFERENCE_BACKSTOC')
alter table BackStock_Detail
   drop constraint FK_BACKSTOC_REFERENCE_BACKSTOC
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('BackStock_Detail') and o.name = 'FK_BACKSTOC_REFERENCE_PRODUCT')
alter table BackStock_Detail
   drop constraint FK_BACKSTOC_REFERENCE_PRODUCT
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Buy') and o.name = 'FK_BUY_REFERENCE_DEPT')
alter table Buy
   drop constraint FK_BUY_REFERENCE_DEPT
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Buy') and o.name = 'FK_BUY_REFERENCE_EMPLOYEE')
alter table Buy
   drop constraint FK_BUY_REFERENCE_EMPLOYEE
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('BuyOrder') and o.name = 'FK_BUYORDER_REFERENCE_DEPT')
alter table BuyOrder
   drop constraint FK_BUYORDER_REFERENCE_DEPT
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('BuyOrder') and o.name = 'FK_BUYORDER_REFERENCE_SUPPLIER')
alter table BuyOrder
   drop constraint FK_BUYORDER_REFERENCE_SUPPLIER
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('BuyOrder') and o.name = 'FK_BUYORDER_REFERENCE_EMPLOYEE')
alter table BuyOrder
   drop constraint FK_BUYORDER_REFERENCE_EMPLOYEE
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('BuyOrder_Detail') and o.name = 'FK_BUYORDER_REFERENCE_BUYORDER')
alter table BuyOrder_Detail
   drop constraint FK_BUYORDER_REFERENCE_BUYORDER
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('BuyOrder_Detail') and o.name = 'FK_BUYORDER_REFERENCE_PRODUCT')
alter table BuyOrder_Detail
   drop constraint FK_BUYORDER_REFERENCE_PRODUCT
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Buy_Detail') and o.name = 'FK_BUY_DETA_REFERENCE_BUY')
alter table Buy_Detail
   drop constraint FK_BUY_DETA_REFERENCE_BUY
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Buy_Detail') and o.name = 'FK_BUY_DETA_REFERENCE_PRODUCT')
alter table Buy_Detail
   drop constraint FK_BUY_DETA_REFERENCE_PRODUCT
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Dept_Customer') and o.name = 'FK_DEPT_CUS_REFERENCE_DEPT')
alter table Dept_Customer
   drop constraint FK_DEPT_CUS_REFERENCE_DEPT
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Dept_Customer') and o.name = 'FK_DEPT_CUS_REFERENCE_CUSTOMER')
alter table Dept_Customer
   drop constraint FK_DEPT_CUS_REFERENCE_CUSTOMER
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Dept_Supplier') and o.name = 'FK_DEPT_SUP_REFERENCE_DEPT')
alter table Dept_Supplier
   drop constraint FK_DEPT_SUP_REFERENCE_DEPT
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Dept_Supplier') and o.name = 'FK_DEPT_SUP_REFERENCE_SUPPLIER')
alter table Dept_Supplier
   drop constraint FK_DEPT_SUP_REFERENCE_SUPPLIER
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('EnterStock') and o.name = 'FK_ENTERSTO_REFERENCE_DEPT')
alter table EnterStock
   drop constraint FK_ENTERSTO_REFERENCE_DEPT
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('EnterStock') and o.name = 'FK_ENTERSTO_REFERENCE_STOREHOU')
alter table EnterStock
   drop constraint FK_ENTERSTO_REFERENCE_STOREHOU
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('EnterStock') and o.name = 'FK_ENTERSTO_REFERENCE_EMPLOYEE')
alter table EnterStock
   drop constraint FK_ENTERSTO_REFERENCE_EMPLOYEE
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('EnterStock_Detail') and o.name = 'FK_ENTERSTO_REFERENCE_ENTERSTO')
alter table EnterStock_Detail
   drop constraint FK_ENTERSTO_REFERENCE_ENTERSTO
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('EnterStock_Detail') and o.name = 'FK_ENTERSTO_REFERENCE_PRODUCT')
alter table EnterStock_Detail
   drop constraint FK_ENTERSTO_REFERENCE_PRODUCT
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('LeaveStock') and o.name = 'FK_LEAVESTO_REFERENCE_DEPT')
alter table LeaveStock
   drop constraint FK_LEAVESTO_REFERENCE_DEPT
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('LeaveStock') and o.name = 'FK_LEAVESTO_REFERENCE_STOREHOU')
alter table LeaveStock
   drop constraint FK_LEAVESTO_REFERENCE_STOREHOU
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('LeaveStock_Detail') and o.name = 'FK_LEAVESTO_REFERENCE_BACKSTOC')
alter table LeaveStock_Detail
   drop constraint FK_LEAVESTO_REFERENCE_BACKSTOC
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('LeaveStock_Detail') and o.name = 'FK_LEAVESTO_REFERENCE_PRODUCT')
alter table LeaveStock_Detail
   drop constraint FK_LEAVESTO_REFERENCE_PRODUCT
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Product') and o.name = 'FK_PRODUCT_REFERENCE_PRODUCTL')
alter table Product
   drop constraint FK_PRODUCT_REFERENCE_PRODUCTL
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Product') and o.name = 'FK_PRODUCT_REFERENCE_PRODUCTS')
alter table Product
   drop constraint FK_PRODUCT_REFERENCE_PRODUCTS
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Product') and o.name = 'FK_PRODUCT_REFERENCE_PRODUCTU')
alter table Product
   drop constraint FK_PRODUCT_REFERENCE_PRODUCTU
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('ProductList') and o.name = 'FK_PRODUCTL_REFERENCE_PRODUCTC')
alter table ProductList
   drop constraint FK_PRODUCTL_REFERENCE_PRODUCTC
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Product_Supplier') and o.name = 'FK_PRODUCT__REFERENCE_SUPPLIER')
alter table Product_Supplier
   drop constraint FK_PRODUCT__REFERENCE_SUPPLIER
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Product_Supplier') and o.name = 'FK_PRODUCT__REFERENCE_PRODUCT')
alter table Product_Supplier
   drop constraint FK_PRODUCT__REFERENCE_PRODUCT
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Sale') and o.name = 'FK_SALE_REFERENCE_DEPT')
alter table Sale
   drop constraint FK_SALE_REFERENCE_DEPT
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Sale') and o.name = 'FK_SALE_REFERENCE_EMPLOYEE')
alter table Sale
   drop constraint FK_SALE_REFERENCE_EMPLOYEE
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Sale_Detail') and o.name = 'FK_SALE_DET_REFERENCE_SALE')
alter table Sale_Detail
   drop constraint FK_SALE_DET_REFERENCE_SALE
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Sale_Detail') and o.name = 'FK_SALE_DET_REFERENCE_PRODUCT')
alter table Sale_Detail
   drop constraint FK_SALE_DET_REFERENCE_PRODUCT
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('StockPile') and o.name = 'FK_STOCKPIL_REFERENCE_DEPT')
alter table StockPile
   drop constraint FK_STOCKPIL_REFERENCE_DEPT
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('StockPile') and o.name = 'FK_STOCKPIL_REFERENCE_STOREHOU')
alter table StockPile
   drop constraint FK_STOCKPIL_REFERENCE_STOREHOU
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('StockPile') and o.name = 'FK_STOCKPIL_REFERENCE_PRODUCT')
alter table StockPile
   drop constraint FK_STOCKPIL_REFERENCE_PRODUCT
go

if exists (select 1
            from  sysobjects
           where  id = object_id('BackSale')
            and   type = 'U')
   drop table BackSale
go

if exists (select 1
            from  sysobjects
           where  id = object_id('BackSale_Detail')
            and   type = 'U')
   drop table BackSale_Detail
go

if exists (select 1
            from  sysobjects
           where  id = object_id('BackStock')
            and   type = 'U')
   drop table BackStock
go

if exists (select 1
            from  sysobjects
           where  id = object_id('BackStock_Detail')
            and   type = 'U')
   drop table BackStock_Detail
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Buy')
            and   type = 'U')
   drop table Buy
go

if exists (select 1
            from  sysobjects
           where  id = object_id('BuyOrder')
            and   type = 'U')
   drop table BuyOrder
go

if exists (select 1
            from  sysobjects
           where  id = object_id('BuyOrder_Detail')
            and   type = 'U')
   drop table BuyOrder_Detail
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Buy_Detail')
            and   type = 'U')
   drop table Buy_Detail
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Customer')
            and   type = 'U')
   drop table Customer
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Dept')
            and   type = 'U')
   drop table Dept
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Dept_Customer')
            and   type = 'U')
   drop table Dept_Customer
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Dept_Supplier')
            and   type = 'U')
   drop table Dept_Supplier
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Employee')
            and   type = 'U')
   drop table Employee
go

if exists (select 1
            from  sysobjects
           where  id = object_id('EnterStock')
            and   type = 'U')
   drop table EnterStock
go

if exists (select 1
            from  sysobjects
           where  id = object_id('EnterStock_Detail')
            and   type = 'U')
   drop table EnterStock_Detail
go

if exists (select 1
            from  sysobjects
           where  id = object_id('LeaveStock')
            and   type = 'U')
   drop table LeaveStock
go

if exists (select 1
            from  sysobjects
           where  id = object_id('LeaveStock_Detail')
            and   type = 'U')
   drop table LeaveStock_Detail
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Product')
            and   type = 'U')
   drop table Product
go

if exists (select 1
            from  sysobjects
           where  id = object_id('ProductClass')
            and   type = 'U')
   drop table ProductClass
go

if exists (select 1
            from  sysobjects
           where  id = object_id('ProductList')
            and   type = 'U')
   drop table ProductList
go

if exists (select 1
            from  sysobjects
           where  id = object_id('ProductSpec')
            and   type = 'U')
   drop table ProductSpec
go

if exists (select 1
            from  sysobjects
           where  id = object_id('ProductUnit')
            and   type = 'U')
   drop table ProductUnit
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Product_Supplier')
            and   type = 'U')
   drop table Product_Supplier
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Sale')
            and   type = 'U')
   drop table Sale
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Sale_Detail')
            and   type = 'U')
   drop table Sale_Detail
go

if exists (select 1
            from  sysobjects
           where  id = object_id('StockPile')
            and   type = 'U')
   drop table StockPile
go

if exists (select 1
            from  sysobjects
           where  id = object_id('StoreHouse')
            and   type = 'U')
   drop table StoreHouse
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Supplier')
            and   type = 'U')
   drop table Supplier
go

if exists (select 1
            from  sysobjects
           where  id = object_id('"User"')
            and   type = 'U')
   drop table "User"
go
Create database PurchaseSaleStock
GO
use PurchaseSaleStock
GO


/*==============================================================*/
/* Table: BackSale                                              */
/*==============================================================*/
create table BackSale (
   BackSale_ID          int                  not null,
   BackDate             datetime             not null,
   Dept_ID              int                  not null,
   StoreHouse_ID        int                  not null,
   Employee             int                  not null,
   Remark               varchar(250)         not null,
   constraint PK_BACKSALE primary key (BackSale_ID)
)
go

/*==============================================================*/
/* Table: BackSale_Detail                                       */
/*==============================================================*/
create table BackSale_Detail (
   BackSale_ID          int                  not null,
   Product_ID           int                  not null,
   BackStock_ID         int                  null,
   Quantity             int                  not null,
   Price                money                null,
   constraint PK_BACKSALE_DETAIL primary key (BackSale_ID, Product_ID)
)
go

/*==============================================================*/
/* Table: BackStock                                             */
/*==============================================================*/
create table BackStock (
   BackStock_ID         int                  not null,
   BackDate             datetime             not null,
   Dept_ID              int                  not null,
   StoreHouse_ID        int                  not null,
   Employee_ID          int                  not null,
   Remark               varchar(250)         not null,
   constraint PK_BACKSTOCK primary key (BackStock_ID)
)
go

/*==============================================================*/
/* Table: BackStock_Detail                                      */
/*==============================================================*/
create table BackStock_Detail (
   BackStock_ID         int                  not null,
   Product_ID           int                  not null,
   Quantity             int                  not null,
   Price                money                null,
   constraint PK_BACKSTOCK_DETAIL primary key (BackStock_ID, Product_ID)
)
go

/*==============================================================*/
/* Table: Buy                                                   */
/*==============================================================*/
create table Buy (
   Buy_ID               int                  not null,
   ComeDate             datetime             not null,
   Dept_ID              int                  not null,
   Employee_ID          int                  not null,
   constraint PK_BUY primary key (Buy_ID)
)
go

/*==============================================================*/
/* Table: BuyOrder                                              */
/*==============================================================*/
create table BuyOrder (
   Buyorder_ID          int                  not null,
   WriteDate            datetime             not null,
   InsureDate           datetime             not null,
   EndDate              datetime             not null,
   Dept_ID              int                  not null,
   Supplier_ID          int                  not null,
   Employee_ID          int                  not null,
   constraint PK_BUYORDER primary key (Buyorder_ID)
)
go

/*==============================================================*/
/* Table: BuyOrder_Detail                                       */
/*==============================================================*/
create table BuyOrder_Detail (
   BuyOrder_ID          int                  not null,
   Product_ID           int                  not null,
   Buy_Buyorder_ID      int                  null,
   Quantity             int                  not null,
   Price                money                not null,
   constraint PK_BUYORDER_DETAIL primary key (BuyOrder_ID, Product_ID)
)
go

/*==============================================================*/
/* Table: Buy_Detail                                            */
/*==============================================================*/
create table Buy_Detail (
   Buy_ID               int                  not null,
   Product_ID           int                  not null,
   Quantity             int                  not null,
   Price                money                not null,
   constraint PK_BUY_DETAIL primary key (Buy_ID, Product_ID)
)
go

/*==============================================================*/
/* Table: Customer                                              */
/*==============================================================*/
create table Customer (
   Customer_ID          int                  not null,
   Customer_Name        varchar(250)         not null,
   Customer_Address     varchar(250)         not null,
   Phone                varchar(25)          not null,
   Fax                  varchar(25)          not null,
   PostalCode           varchar(10)          not null,
   ConstactPerson       varchar(20)          not null,
   constraint PK_CUSTOMER primary key (Customer_ID)
)
go

/*==============================================================*/
/* Table: Dept                                                  */
/*==============================================================*/
create table Dept (
   Dept_ID              int                  not null,
   Dept_Name            varchar(30)          not null,
   Remark               varchar(250)         not null,
   constraint PK_DEPT primary key (Dept_ID)
)
go

/*==============================================================*/
/* Table: Dept_Customer                                         */
/*==============================================================*/
create table Dept_Customer (
   Dept_ID              int                  not null,
   Customer_ID          int                  not null,
   constraint PK_DEPT_CUSTOMER primary key (Dept_ID, Customer_ID)
)
go

/*==============================================================*/
/* Table: Dept_Supplier                                         */
/*==============================================================*/
create table Dept_Supplier (
   Dept_ID              int                  not null,
   Supplier_ID          int                  not null,
   constraint PK_DEPT_SUPPLIER primary key (Dept_ID, Supplier_ID)
)
go

/*==============================================================*/
/* Table: Employee                                              */
/*==============================================================*/
create table Employee (
   Employee_ID          int                  not null,
   Dept_ID              int                  not null,
   Name                 varchar(30)          not null,
   Duty                 varchar(20)          not null,
   Gender               varchar(6)           not null,
   BithDate             datetime             not null,
   HireDate             datetime             null,
   MatureDate           datetime             null,
   IdentityCard         varchar(20)          not null,
   Address              varchar(250)         not null,
   Phone                varchar(25)          not null,
   Email                varchar(30)          null
)
go

/*==============================================================*/
/* Table: EnterStock                                            */
/*==============================================================*/
create table EnterStock (
   EnterStock_ID        int                  not null,
   EnterDate            datetime             not null,
   Dept_ID              int                  not null,
   StoreHouse_ID        int                  not null,
   Employee             int                  not null,
   constraint PK_ENTERSTOCK primary key (EnterStock_ID)
)
go

/*==============================================================*/
/* Table: EnterStock_Detail                                     */
/*==============================================================*/
create table EnterStock_Detail (
   EnterStock_ID        int                  not null,
   Product              int                  not null,
   Product_ID           int                  null,
   Quantity             int                  null,
   constraint PK_ENTERSTOCK_DETAIL primary key (EnterStock_ID, Product)
)
go

/*==============================================================*/
/* Table: LeaveStock                                            */
/*==============================================================*/
create table LeaveStock (
   LeaveStock_ID        int                  not null,
   LeaveDate            datetime             not null,
   StoreHouse_ID        int                  not null,
   ToStoreHouse_ID      int                  not null,
   Employee_ID          int                  not null,
   constraint PK_LEAVESTOCK primary key (LeaveStock_ID)
)
go

/*==============================================================*/
/* Table: LeaveStock_Detail                                     */
/*==============================================================*/
create table LeaveStock_Detail (
   LeaveStock_ID        int                  not null,
   Product_ID           int                  not null,
   Quantity             int                  not null,
   Price                money                null,
   constraint PK_LEAVESTOCK_DETAIL primary key (LeaveStock_ID, Product_ID)
)
go

/*==============================================================*/
/* Table: Product                                               */
/*==============================================================*/
create table Product (
   ProductList_ID       int                  not null,
   Product_ID           int                  not null,
   Product_Name         varchar(30)          not null,
   ProductSpec_ID       int                  not null,
   ProductUnit_ID       int                  not null,
   Price                money                null,
   Employee_ID          int                  not null,
   CreateDate           datetime             null,
   Remark               varchar(250)         null,
   constraint PK_PRODUCT primary key (Product_ID)
)
go

/*==============================================================*/
/* Table: ProductClass                                          */
/*==============================================================*/
create table ProductClass (
   ProductClass_ID      int                  not null,
   Product_Name         varchar(30)          not null,
   Employee_ID          int                  not null,
   CreateDate           datetime             null,
   Remark               varchar(250)         null,
   constraint PK_PRODUCTCLASS primary key (ProductClass_ID)
)
go

/*==============================================================*/
/* Table: ProductList                                           */
/*==============================================================*/
create table ProductList (
   ProductClass_ID      int                  not null,
   ProductList_ID       int                  not null,
   ProductList_Name     varchar(30)          not null,
   Employee_ID          int                  not null,
   CreateDate           datetime             null,
   Remark               varchar(250)         null,
   constraint PK_PRODUCTLIST primary key (ProductList_ID)
)
go

/*==============================================================*/
/* Table: ProductSpec                                           */
/*==============================================================*/
create table ProductSpec (
   ProductSpec_ID       int                  not null,
   ProductSpec_Name     varchar(30)          not null,
   Employee_ID          int                  not null,
   CreateDate           datetime             null,
   Remark               varchar(250)         null,
   constraint PK_PRODUCTSPEC primary key (ProductSpec_ID)
)
go

/*==============================================================*/
/* Table: ProductUnit                                           */
/*==============================================================*/
create table ProductUnit (
   ProductUnit_ID       int                  not null,
   ProductUnit_Name     varchar(30)          not null,
   Employee_ID          int                  not null,
   CreateDate           datetime             null,
   Remark               varchar(250)         null,
   constraint PK_PRODUCTUNIT primary key (ProductUnit_ID)
)
go

/*==============================================================*/
/* Table: Product_Supplier                                      */
/*==============================================================*/
create table Product_Supplier (
   Product_ID           int                  not null,
   Supplier_ID          int                  not null,
   constraint PK_PRODUCT_SUPPLIER primary key (Product_ID, Supplier_ID)
)
go

/*==============================================================*/
/* Table: Sale                                                  */
/*==============================================================*/
create table Sale (
   Sale_ID              int                  not null,
   SaleDate             datetime             not null,
   Dept_ID              int                  not null,
   Employee_ID          int                  not null
)
go

/*==============================================================*/
/* Table: Sale_Detail                                           */
/*==============================================================*/
create table Sale_Detail (
   Sale_ID              int                  not null,
   Product_ID           int                  not null,
   Quantity             int                  not null,
   Price                money                not null,
   Discount             int                  null,
   constraint PK_SALE_DETAIL primary key (Sale_ID, Product_ID)
)
go

/*==============================================================*/
/* Table: StockPile                                             */
/*==============================================================*/
create table StockPile (
   StockPile_ID         int                  not null,
   Dept_ID              int                  not null,
   StoreHouse_ID        int                  not null,
   Product_ID           int                  not null,
   FirstEnterDate       datetime             not null,
   LastLeaveDate        datetime             null,
   Quantity             int                  not null,
   constraint PK_STOCKPILE primary key (StockPile_ID)
)
go

/*==============================================================*/
/* Table: StoreHouse                                            */
/*==============================================================*/
create table StoreHouse (
   StoreHouse_ID        int                  not null,
   Address              varchar(250)         not null,
   Phone                varchar(25)          not null,
   Employee_ID          int                  not null,
   CreateDate           datetime             null,
   constraint PK_STOREHOUSE primary key (StoreHouse_ID)
)
go

/*==============================================================*/
/* Table: Supplier                                              */
/*==============================================================*/
create table Supplier (
   Supplier_ID          int                  not null,
   Name                 varchar(250)         not null,
   Address              varchar(250)         not null,
   Phone                varchar(25)          not null,
   Fax                  varchar(10)          not null,
   PostalCode           varchar(10)          not null,
   ConstactPerson       varchar(20)          not null,
   constraint PK_SUPPLIER primary key (Supplier_ID)
)
go

/*==============================================================*/
/* Table: "User"                                                */
/*==============================================================*/
create table "User" (
   User_Id              varchar(6)           not null,
   User_Pwd             varchar(8)           not null,
   Again_Pwd            varchar(8)           not null,
   User_Auth            varchar(1)           not null,
   Auth_Type            varchar(1)           not null,
   User_Status          varchar(1)           not null,
   Create_User          varchar(6)           not null,
   Create_Date          varchar(7)           not null,
   Create_Time          varchar(6)           not null,
   Appr_User            varchar(6)           not null,
   Appr_Date            varchar(7)           not null,
   Appr_Time            varchar(6)           not null
)
go

alter table BackSale
   add constraint FK_BACKSALE_REFERENCE_DEPT foreign key (Dept_ID)
      references Dept (Dept_ID)
go

alter table BackSale
   add constraint FK_BACKSALE_REFERENCE_STOREHOU foreign key (StoreHouse_ID)
      references StoreHouse (StoreHouse_ID)
go

alter table BackSale
   add constraint FK_BACKSALE_REFERENCE_EMPLOYEE foreign key (Employee)
      references Employee (Employee_ID)
go

alter table BackSale_Detail
   add constraint FK_BACKSALE_REFERENCE_BACKSTOC foreign key (BackStock_ID)
      references BackStock (BackStock_ID)
go

alter table BackSale_Detail
   add constraint FK_BACKSALE_REFERENCE_PRODUCT foreign key (Product_ID)
      references Product (Product_ID)
go

alter table BackStock
   add constraint FK_BACKSTOC_REFERENCE_DEPT foreign key (Dept_ID)
      references Dept (Dept_ID)
go

alter table BackStock
   add constraint FK_BACKSTOC_REFERENCE_STOREHOU foreign key (StoreHouse_ID)
      references StoreHouse (StoreHouse_ID)
go

alter table BackStock
   add constraint FK_BACKSTOC_REFERENCE_EMPLOYEE foreign key (Employee_ID)
      references Employee (Employee_ID)
go

alter table BackStock_Detail
   add constraint FK_BACKSTOC_REFERENCE_BACKSTOC foreign key (BackStock_ID)
      references BackStock (BackStock_ID)
go

alter table BackStock_Detail
   add constraint FK_BACKSTOC_REFERENCE_PRODUCT foreign key (Product_ID)
      references Product (Product_ID)
go

alter table Buy
   add constraint FK_BUY_REFERENCE_DEPT foreign key (Dept_ID)
      references Dept (Dept_ID)
go

alter table Buy
   add constraint FK_BUY_REFERENCE_EMPLOYEE foreign key (Employee_ID)
      references Employee (Employee_ID)
go

alter table BuyOrder
   add constraint FK_BUYORDER_REFERENCE_DEPT foreign key (Dept_ID)
      references Dept (Dept_ID)
go

alter table BuyOrder
   add constraint FK_BUYORDER_REFERENCE_SUPPLIER foreign key (Supplier_ID)
      references Supplier (Supplier_ID)
go

alter table BuyOrder
   add constraint FK_BUYORDER_REFERENCE_EMPLOYEE foreign key (Employee_ID)
      references Employee (Employee_ID)
go

alter table BuyOrder_Detail
   add constraint FK_BUYORDER_REFERENCE_BUYORDER foreign key (Buy_Buyorder_ID)
      references BuyOrder (Buyorder_ID)
go

alter table BuyOrder_Detail
   add constraint FK_BUYORDER_REFERENCE_PRODUCT foreign key (Product_ID)
      references Product (Product_ID)
go

alter table Buy_Detail
   add constraint FK_BUY_DETA_REFERENCE_BUY foreign key (Buy_ID)
      references Buy (Buy_ID)
go

alter table Buy_Detail
   add constraint FK_BUY_DETA_REFERENCE_PRODUCT foreign key (Product_ID)
      references Product (Product_ID)
go

alter table Dept_Customer
   add constraint FK_DEPT_CUS_REFERENCE_DEPT foreign key (Dept_ID)
      references Dept (Dept_ID)
go

alter table Dept_Customer
   add constraint FK_DEPT_CUS_REFERENCE_CUSTOMER foreign key (Customer_ID)
      references Customer (Customer_ID)
go

alter table Dept_Supplier
   add constraint FK_DEPT_SUP_REFERENCE_DEPT foreign key (Dept_ID)
      references Dept (Dept_ID)
go

alter table Dept_Supplier
   add constraint FK_DEPT_SUP_REFERENCE_SUPPLIER foreign key (Supplier_ID)
      references Supplier (Supplier_ID)
go

alter table EnterStock
   add constraint FK_ENTERSTO_REFERENCE_DEPT foreign key (Dept_ID)
      references Dept (Dept_ID)
go

alter table EnterStock
   add constraint FK_ENTERSTO_REFERENCE_STOREHOU foreign key (StoreHouse_ID)
      references StoreHouse (StoreHouse_ID)
go

alter table EnterStock
   add constraint FK_ENTERSTO_REFERENCE_EMPLOYEE foreign key (Employee)
      references Employee (Employee_ID)
go

alter table EnterStock_Detail
   add constraint FK_ENTERSTO_REFERENCE_ENTERSTO foreign key (EnterStock_ID)
      references EnterStock (EnterStock_ID)
go

alter table EnterStock_Detail
   add constraint FK_ENTERSTO_REFERENCE_PRODUCT foreign key (Product_ID)
      references Product (Product_ID)
go

alter table LeaveStock
   add constraint FK_LEAVESTO_REFERENCE_DEPT foreign key (ToStoreHouse_ID)
      references Dept (Dept_ID)
go

alter table LeaveStock
   add constraint FK_LEAVESTO_REFERENCE_STOREHOU foreign key (StoreHouse_ID)
      references StoreHouse (StoreHouse_ID)
go

alter table LeaveStock_Detail
   add constraint FK_LEAVESTO_REFERENCE_BACKSTOC foreign key (LeaveStock_ID)
      references BackStock (BackStock_ID)
go

alter table LeaveStock_Detail
   add constraint FK_LEAVESTO_REFERENCE_PRODUCT foreign key (Product_ID)
      references Product (Product_ID)
go

alter table Product
   add constraint FK_PRODUCT_REFERENCE_PRODUCTL foreign key (ProductList_ID)
      references ProductList (ProductList_ID)
go

alter table Product
   add constraint FK_PRODUCT_REFERENCE_PRODUCTS foreign key (ProductSpec_ID)
      references ProductSpec (ProductSpec_ID)
go

alter table Product
   add constraint FK_PRODUCT_REFERENCE_PRODUCTU foreign key (ProductUnit_ID)
      references ProductUnit (ProductUnit_ID)
go

alter table ProductList
   add constraint FK_PRODUCTL_REFERENCE_PRODUCTC foreign key (ProductClass_ID)
      references ProductClass (ProductClass_ID)
go

alter table Product_Supplier
   add constraint FK_PRODUCT__REFERENCE_SUPPLIER foreign key (Supplier_ID)
      references Supplier (Supplier_ID)
go

alter table Product_Supplier
   add constraint FK_PRODUCT__REFERENCE_PRODUCT foreign key (Product_ID)
      references Product (Product_ID)
go

alter table Sale
   add constraint FK_SALE_REFERENCE_DEPT foreign key (Dept_ID)
      references Dept (Dept_ID)
go

alter table Sale
   add constraint FK_SALE_REFERENCE_EMPLOYEE foreign key (Employee_ID)
      references Employee (Employee_ID)
go

alter table Sale_Detail
   add constraint FK_SALE_DET_REFERENCE_SALE foreign key (Sale_ID)
      references Sale (Sale_ID)
go

alter table Sale_Detail
   add constraint FK_SALE_DET_REFERENCE_PRODUCT foreign key (Product_ID)
      references Product (Product_ID)
go

alter table StockPile
   add constraint FK_STOCKPIL_REFERENCE_DEPT foreign key (Dept_ID)
      references Dept (Dept_ID)
go

alter table StockPile
   add constraint FK_STOCKPIL_REFERENCE_STOREHOU foreign key (StoreHouse_ID)
      references StoreHouse (StoreHouse_ID)
go

alter table StockPile
   add constraint FK_STOCKPIL_REFERENCE_PRODUCT foreign key (Product_ID)
      references Product (Product_ID)
go

